
package persistencia;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import utiles.*;

import modeloDatos.*;

public class DAOTutores {
	private Connection conn;

	public DAOTutores(){
		
	}
	
	public void finalize(){
        ConnectionManager.getInstance().checkIn(conn);
    }
	
	public boolean persistCurriculum(int idTutor, String cv){
		conn = ConnectionManager.getInstance().checkOutLportal();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM dlfileentry WHERE uuid_ = ?";
		Boolean res = false;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, cv);
			rs = stmt.executeQuery();
			if(rs.next()){
				res = true;
			}
			
			
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return res;
	}
	public boolean persistFoto(int idTutor, String foto){
		conn = ConnectionManager.getInstance().checkOutLportal();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM dlfileentry WHERE uuid_ = ?";
		Boolean res = false;
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, foto);
			rs = stmt.executeQuery();
			if(rs.next()){
				res = true;
			}
			
			
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return res;
	}

	public void eliminarFoto(String foto) {
		conn = ConnectionManager.getInstance().checkOutLportal();
		PreparedStatement stmt = null;
		String sql = "DELETE FROM dlfileentry WHERE uuid_=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, foto);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		
	}
	
	public boolean eliminarTutor(int idTutor){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "DELETE FROM tutores WHERE idTutor=?";
		try {
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idTutor);
			stmt.executeUpdate();
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return true;
	}
	
	public String setCurriculum(int idTutor, String url){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "UPDATE tutores " +
				"SET idCurriculum=? WHERE idTutor=?";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, url);
			stmt.setInt(2, idTutor);		
			stmt.execute();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
            return "ERROR al interaccionar con la Base de Datos.";
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return "";	
	}
	
	public String actualizarTutor(int idTutor, List<String> detalles){
		// 0- NOMBRE, 1- APELLIDOS, 2 - FECHA DE NACIMIENTO, 3 - EMAIL, 4 - FIJO, 5 - MOVIL, 
		// 6- FAX, 7 - DIRECCION, 8 - LOCALIDAD, 9 -  PROVINCIA, 10 - CP , 11 - SEGSOCIAL, 
		// 12- NIF, 13 - SEXO , 14 - ENNOMINA, 15 - OBSERVACIONES
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "UPDATE tutores " +
				"SET nombre=?,apellidos=?,fechaNacimiento=?,email=?,telefonoFijo=?,telefonoMovil=?," +
				"fax=?,direccion=?,localidad=?,provincia=?,cp=?," +
				"numeroSS=?,nif=?,sexo=?,enNomina=?,observaciones=? WHERE idTutor=?";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, detalles.get(0));
			stmt.setString(2, detalles.get(1));
			if(!detalles.get(2).equals("")){
				java.sql.Date fechaNac = java.sql.Date.valueOf(detalles.get(2));
				stmt.setDate(3, fechaNac);
			}else{
				stmt.setDate(3, null);
			}
			
			stmt.setString(4, detalles.get(3));
			if(detalles.get(4).equals(""))
				stmt.setInt(5, -1);
			else
				stmt.setInt(5, Integer.parseInt(detalles.get(4)));
			if(detalles.get(5).equals(""))
				stmt.setInt(6, -1);
			else
				stmt.setInt(6,Integer.parseInt(detalles.get(5)));
			if(detalles.get(6).equals(""))
				stmt.setInt(7, -1);
			else
				stmt.setInt(7, Integer.parseInt(detalles.get(6))); 
			stmt.setString(8, detalles.get(7));
			stmt.setString(9, detalles.get(8));
			stmt.setString(10, detalles.get(9));
			if(detalles.get(10).equals(""))
				stmt.setInt(11, -1);
			else
				stmt.setInt(11, Integer.parseInt(detalles.get(10)));
			if(detalles.get(11).equals(""))
				stmt.setInt(12, -1);
			else
				stmt.setInt(12, Integer.parseInt(detalles.get(11)));
			stmt.setString(13, detalles.get(12));
			stmt.setString(14, detalles.get(13));
			stmt.setBoolean(15,Boolean.parseBoolean(detalles.get(14)));
			stmt.setString(16, detalles.get(15));
			stmt.setInt(17,idTutor);
			
			
			stmt.execute();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
            return "ERROR al interaccionar con la Base de Datos.";
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return "";
	}
	
	public Tutor insertarTutor(){
		Tutor tutor = null;
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "INSERT INTO tutores (idTutor) VALUES (NULL)";

		try{
		stmt = conn.prepareStatement(sql);
		stmt.execute();
			String sql2 = "SELECT MAX(idTutor) FROM tutores";
			try{
				stmt = conn.prepareStatement(sql2);
				ResultSet rs = stmt.executeQuery();
				if(rs.next()){		
					int idTutor = rs.getInt(1);
					tutor = new Tutor(idTutor);
				}
				
			}catch(SQLException e){
				System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	            
			}finally {
	        	finalize();
	            try {
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
			
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
            
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		
		return tutor;
	}
	
	
	public List<Tutor> getTutoresCampos(String[] campos){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String cadenaCampos = Util.ArrayToConjuntoSQL(campos);
		String sql = "SELECT DISTINCT t.idTutor, t.nombre, t.apellidos, t.fechaNacimiento, t.email, t.telefonoFijo, " +
				"t.telefonoMovil, t.fax, t.direccion, t.localidad, t.provincia, t.cp, t.numeroSS, t.nif, t.sexo, t.enNomina," +
				"t.observaciones, t.idCurriculum, t.idFoto FROM tutores t, tutorcampo tc WHERE tc.idCampo IN " + cadenaCampos +" AND tc.idTutor = t.idTutor";
		ResultSet rs = null;
		List<Tutor> listaT = new ArrayList<Tutor>();
		try{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Tutor t = new Tutor(rs.getInt("idTutor"));
				t.setNombre(rs.getString("nombre"));
				t.setApellidos(rs.getString("apellidos"));
				t.setFechaNacimiento(rs.getDate("fechaNacimiento"));
				t.setEmail(rs.getString("email"));
				t.setFijo(rs.getInt("telefonoFijo"));
				t.setMovil(rs.getInt("telefonoMovil"));
				t.setFax(rs.getInt("fax"));
				t.setDireccion(rs.getString("direccion"));
				t.setLocalidad(rs.getString("localidad"));
				t.setProvincia(rs.getString("provincia"));
				t.setcPostal(rs.getInt("cp"));
				t.setSSocial(rs.getInt("numeroSS"));
				t.setNIF(rs.getString("nif"));
				t.setSexo(rs.getString("sexo").charAt(0));
				t.setEnNomina(rs.getBoolean("enNomina"));
				t.setObservaciones(rs.getString("observaciones"));
				t.setCurriculum(rs.getString("idCurriculum"));
				t.setFoto(rs.getString("idFoto"));
				listaT.add(t);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaT;
	}

	public List<Tutor> getTutoresEnNomina(boolean estado){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tutores WHERE enNomina=?";
		ResultSet rs = null;
		List<Tutor> listaT = new ArrayList<Tutor>();
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setBoolean(1, estado);
			rs = stmt.executeQuery();
			while(rs.next()){
				Tutor t = new Tutor(rs.getInt("idTutor"));
				t.setNombre(rs.getString("nombre"));
				t.setApellidos(rs.getString("apellidos"));
				t.setFechaNacimiento(rs.getDate("fechaNacimiento"));
				t.setEmail(rs.getString("email"));
				t.setFijo(rs.getInt("telefonoFijo"));
				t.setMovil(rs.getInt("telefonoMovil"));
				t.setFax(rs.getInt("fax"));
				t.setDireccion(rs.getString("direccion"));
				t.setLocalidad(rs.getString("localidad"));
				t.setProvincia(rs.getString("provincia"));
				t.setcPostal(rs.getInt("cp"));
				t.setSSocial(rs.getInt("numeroSS"));
				t.setNIF(rs.getString("nif"));
				t.setSexo(rs.getString("sexo").charAt(0));
				t.setEnNomina(rs.getBoolean("enNomina"));
				t.setObservaciones(rs.getString("observaciones"));
				t.setCurriculum(rs.getString("idCurriculum"));
				t.setFoto(rs.getString("idFoto"));
				listaT.add(t);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaT;
	}
	
	public Tutor getTutorNIF(String nif){
		Tutor t  = null;
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tutores WHERE nif = ?";
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, nif);
			rs = stmt.executeQuery();
			if(rs.next()){
				t = new Tutor(rs.getInt("idTutor"));
				t.setNombre(rs.getString("nombre"));
				t.setApellidos(rs.getString("apellidos"));
				t.setFechaNacimiento(rs.getDate("fechaNacimiento"));
				t.setEmail(rs.getString("email"));
				t.setFijo(rs.getInt("telefonoFijo"));
				t.setMovil(rs.getInt("telefonoMovil"));
				t.setFax(rs.getInt("fax"));
				t.setDireccion(rs.getString("direccion"));
				t.setLocalidad(rs.getString("localidad"));
				t.setProvincia(rs.getString("provincia"));
				t.setcPostal(rs.getInt("cp"));
				t.setSSocial(rs.getInt("numeroSS"));
				t.setNIF(rs.getString("nif"));
				t.setSexo(rs.getString("sexo").charAt(0));
				t.setEnNomina(rs.getBoolean("enNomina"));
				t.setObservaciones(rs.getString("observaciones"));
				t.setCurriculum(rs.getString("idCurriculum"));
				t.setFoto(rs.getString("idFoto"));
				
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return t;	
	}
	
	public Tutor getTutorId(int idTutor){
		Tutor t  = null;
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tutores WHERE idTutor = ?";
		ResultSet rs = null;
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, idTutor);
			rs = stmt.executeQuery();
			if(rs.next()){
				t = new Tutor(rs.getInt("idTutor"));
				t.setNombre(rs.getString("nombre"));
				t.setApellidos(rs.getString("apellidos"));
				t.setFechaNacimiento(rs.getDate("fechaNacimiento"));
				t.setEmail(rs.getString("email"));
				t.setFijo(rs.getInt("telefonoFijo"));
				t.setMovil(rs.getInt("telefonoMovil"));
				t.setFax(rs.getInt("fax"));
				t.setDireccion(rs.getString("direccion"));
				t.setLocalidad(rs.getString("localidad"));
				t.setProvincia(rs.getString("provincia"));
				t.setcPostal(rs.getInt("cp"));
				t.setSSocial(rs.getInt("numeroSS"));
				t.setNIF(rs.getString("nif"));
				t.setSexo(rs.getString("sexo").charAt(0));
				t.setEnNomina(rs.getBoolean("enNomina"));
				t.setObservaciones(rs.getString("observaciones"));
				t.setCurriculum(rs.getString("idCurriculum"));
				t.setFoto(rs.getString("idFoto"));
				
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return t;
	}
	public List<Tutor> listarTutores(){
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "SELECT * FROM tutores";
		ResultSet rs = null;
		List<Tutor> listaT = new ArrayList<Tutor>();
		try{
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while(rs.next()){
				Tutor t = new Tutor(rs.getInt("idTutor"));
				t.setNombre(rs.getString("nombre"));
				t.setApellidos(rs.getString("apellidos"));
				t.setFechaNacimiento(rs.getDate("fechaNacimiento"));
				t.setEmail(rs.getString("email"));
				t.setFijo(rs.getInt("telefonoFijo"));
				t.setMovil(rs.getInt("telefonoMovil"));
				t.setFax(rs.getInt("fax"));
				t.setDireccion(rs.getString("direccion"));
				t.setLocalidad(rs.getString("localidad"));
				t.setProvincia(rs.getString("provincia"));
				t.setcPostal(rs.getInt("cp"));
				t.setSSocial(rs.getInt("numeroSS"));
				t.setNIF(rs.getString("nif"));
				t.setSexo(rs.getString("sexo").charAt(0));
				t.setEnNomina(rs.getBoolean("enNomina"));
				t.setObservaciones(rs.getString("observaciones"));
				t.setCurriculum(rs.getString("idCurriculum"));
				t.setFoto(rs.getString("idFoto"));
				listaT.add(t);
			}
		} catch (SQLException ex) {
			System.out.println("Message: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("ErrorCode: " + ex.getErrorCode());
		} finally {
			finalize();
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException ex) {
			}
		}
		return listaT;
		
	}

	public String setFoto(Integer idTutor, String uuid) {
		// TODO Auto-generated method stub
		conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		String sql = "UPDATE tutores " +
				"SET idFoto=? WHERE idTutor=?";
		try{
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, uuid);
			stmt.setInt(2, idTutor);		
			stmt.execute();
		}catch(SQLException e){
			System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
            return "ERROR al interaccionar con la Base de Datos.";
		}finally {
        	finalize();
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
		return "";
		
	}

	

	
}
